<?php
include_once 'classes/DBCon.php';
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.sr
 */

/**
 * Description of Announcement
 *
 * @author Moofie
 */
class Announcement {
    private $con;
    private $aid;
    private $message;
    private $publishDate;
    private $title;
    private $uid;
    private $type;
    private $graphic;
    private $videoEmbLink;
    private $count;
    private $selectAnnouncement = 'SELECT aid, message, publishDate, title, uid, type FROM annoucement WHERE status=? ORDER BY publishDate DESC LIMIT 3;';
    private $selectListAnnouncement = 'SELECT aid, title, message, publishDate, uid, type, graphic, videoEmbLink FROM annoucement WHERE status=?;';
    private $selectAnnounceModify = 'SELECT aid, title, message, publishDate, uid, type, graphic, videoEmbLink FROM annoucement WHERE aid=? AND status=?;';
    private $selectListsAnnouncements2 = 'SELECT aid, title, message, publishDate, uid, type, graphic ,videoEmbLink FROM annoucement WHERE status=?;';
    private $selectAnnouncementDetails = 'SELECT username, aid, message, publishDate, title, u.uid, type, videoEmbLink, graphic FROM annoucement a, users u WHERE a.uid=u.uid AND aid=? AND status=?;';
    private $insertAnnouncement = 'INSERT INTO annoucement(aid,title,message,publishDate,uid,type,graphic,videoEmbLink,status) VALUES (?,?,?,?,?,?,?,?,?);';
    private $selectNewAid = 'SELECT COUNT(aid) FROM annoucement;';
    private $updateRemoveAnnouncement = 'UPDATE annoucement SET status = ? WHERE aid=?;';
    private $selectListDeleteAnnouncement = 'SELECT aid, title, message, publishDate, uid, type, graphic, videoEmbLink FROM annoucement WHERE status=?;';
    private $updateRestoreAnnouncement = 'UPDATE annoucement SET status = ? WHERE aid=?;';
    private $selectListRestoreAnnouncement = 'SELECT aid, title, message, publishDate, uid, type, graphic, videoEmbLink FROM annoucement WHERE status=?;';
    private $selectEmailPromotion = 'SELECT aid, title, message, publishDate, uid, type, graphic, videoEmbLink FROM annoucement WHERE status=?;';
    private $selectAllAvailableAnnouncement = 'SELECT aid, title, message, publishDate, uid, type, graphic, videoEmbLink FROM annoucement WHERE  status=?;';
    private $selectAllAvailableAnnouncement2 = 'SELECT aid, title, message, publishDate, uid, type, graphic, videoEmbLink FROM annoucement WHERE aid=? AND status=?;';
    private $updateAnnouncement = 'UPDATE annoucement SET title = ?, message = ? , type = ? , graphic = ?, videoEmbLink = ? WHERE aid = ?;';
    
    
    function __construct() {
        $this->con = DBCon::connectDB();
    }
    
    
    public function displayAnnouncementBoard(){
        $content = NULL;
        $status = 'Available';
        $announcementArray = array();
        $announcement = NULL;
        
        $pstmtSelect = $this->con->prepare($this->selectAnnouncement);
        $pstmtSelect->bind_param('s', $status);
        $pstmtSelect->bind_result($announcementArray['aid'],$announcementArray['message'],$announcementArray['publishDate'],$announcementArray['title'],$announcementArray['uid'],$announcementArray['type']);
        $pstmtSelect->execute();
        
        while($pstmtSelect->fetch()){
            foreach ($announcementArray as $key => $value){
                $result[$key] = $value;
            }
            $announcement = $result;
            $content .= '<p style="margin:.5em 0 .5em 0">';
            if($result['type']=='General'){
                $content .= '<img src="img/AnnouncementGeneral.png" />&nbsp;';
            }else if($result['type']=='Event'){
                $content .= '<img src="img/AnnouncementEvent.png" />&nbsp;&nbsp;&nbsp;&nbsp;';
            }else{
                $content .= '<img src="img/AnnouncementService.png" />&nbsp;&nbsp;';
            }
            $content .= $result['publishDate'].'&nbsp;&nbsp;';
            $content .= '<a href="announcementDetail.php?permalinkId=';
            $content .= htmlspecialchars($result['aid']) . '">'.$result['title'].'...</a>';
            $content .= '&nbsp;&nbsp;</p>';
            
        }   
        echo $content;
    }
    
        public function displayAnnouncementBoardMaintenance(){
        $content = NULL;
        $announcementArray = array();
        $announcement = NULL;
        
        $pstmtSelect = $this->con->prepare($this->selectAnnouncement);
        $pstmtSelect->bind_result($announcementArray['aid'],$announcementArray['message'],$announcementArray['publishDate'],$announcementArray['title'],$announcementArray['uid'],$announcementArray['type']);
        $pstmtSelect->execute();
        
        while($pstmtSelect->fetch()){
            foreach ($announcementArray as $key => $value){
                $result[$key] = $value;
            }
            $announcement = $result;
            $content .= '<p style="margin:.5em 0 .5em 0">';

            $content .= $result['publishDate'].'&nbsp;&nbsp;';
            $content .= '<a href="announcementDetail.php?permalinkId=';
            $content .= htmlspecialchars($result['aid']) . '">'.$result['title'].'...</a>';
            $content .= '&nbsp;&nbsp;</p>';
            
        }   
        echo $content;
    }
    
    public function listAnnouncement(){
        $content = NULL;
        $status = 'Available';
        $pstmtSelect = $this->con->prepare($this->selectListAnnouncement);
        $pstmtSelect->bind_param('s', $status);
        $pstmtSelect->bind_result($this->aid,$this->title,$this->message,$this->publishDate,$this->uid,$this->type,$this->graphic,$this->videoEmbLink);
        $pstmtSelect->execute();
        $pstmtSelect->store_result();
        
        $content .= '<table bordercolor="#888"  align="center" border="0">';
        while($pstmtSelect->fetch()){
            $content .= '<tr>';
            $content .= '<td>';
            if($this->type=='General'){
                $content .= '<img src="img/AnnouncementGeneral.png" />';
            }else if($this->type=='Event'){
                $content .= '<img src="img/AnnouncementEvent.png" />';
            }else{
                $content .= '<img src="img/AnnouncementService.png" />';
            }      
            $content .= '</td>';
            $content .= '<td>';
            $content .= '<a href="announcementDetail.php?permalinkId=';
            $content .= htmlspecialchars($this->aid) . '">'.$this->title.'</a>';
            $content .= '<td>'.htmlspecialchars($this->publishDate).'</td>';
            $content .= '</td>';
            $content .= '</tr>';
        }
        $content .= '<table>';
        
        
        echo $content;
    }
    
    public function announcementDetails($aid){
        $content = NULL;
        $username = NULL;
        $status = 'Available';

        $pstmtSelect = $this->con->prepare($this->selectAnnouncementDetails);
        $pstmtSelect->bind_param('ss', $aid,$status);
        $pstmtSelect->bind_result($username,$this->aid,$this->message,$this->publishDate,$this->title,$this->uid,$this->type, $this->videoEmbLink, $this->graphic);
        $pstmtSelect->execute();
        $pstmtSelect->store_result();
        
        
        $content .= '<table bordercolor="white" align="center" border="0" style="table-layout: fixed; width: 50%">';
        if($pstmtSelect->fetch()){
            $content .= '<tr>';
            $content .= '<td><h1><i>News</i><br /></h1><br />'.htmlspecialchars($this->publishDate).'</td>';
            $content .= '</tr>';
            $content .= '<tr>';
            $content .= '<td><h3>'.$this->getImageType($this->type).''.htmlspecialchars($this->title).'</h3></td>';
            $content .= '</tr>';
            $content .= '<tr>';
            $newLink = substr($this->videoEmbLink,31);
            $content .= '<td><iframe id="link2"width="380" height="315" src="http://www.youtube.com/embed/'.$newLink.'" frameborder="0" allowfullscreen></iframe><br /><td>';
            $content .='</tr>';
            $content .= '<tr>';
            $content .= '<td><p>'.htmlspecialchars($this->message).'</p></td>';
            $content .= '</tr>';
            if($this->graphic!=null){
            $content .= '<tr>';
            $content .= '<td><img width="380" height="315" src="'.$this->graphic.'" frameborder="0" allowfullscreen></img><br /></td>';
            $content .='</tr>';
            }
            //testing purpose, future implementation
            if($this->aid==3){
            $content .= '<tr>';
            $content .= '<td><img src="img/newyear.jpg" /></td>';
            $content .= '</tr>';
            }
            //testing purpose, future implementation
            
        }else{
            $content .= '<tr>';
            $content .= '<td><h1 align="Center">Page Not Found!</h1></td>';
            $content .= '</tr>';
        }
        
        $content .= '</table>';
        
        echo $content;
        
    }
    
    public function getNewAnnouncementId(){
        $pstmtSelect = $this->con->prepare($this->selectNewAid);
        $pstmtSelect->bind_result($aid);
        $pstmtSelect->execute();
        $pstmtSelect->store_result();
        
        if($pstmtSelect->fetch()){
            return $aid + 1;
        }else{
            return false;
        }
    }
    
    public function createAnnouncment($title,$message,$uid,$type,$graphic,$videoEmbLink,$status){
        $aid = $this->getNewAnnouncementId();
        $publishDate = date("Y-m-d");
        
        $pstmtInsert = $this->con->prepare($this->insertAnnouncement);
        $pstmtInsert->bind_param('issssssss', $aid,$title,$message,$publishDate,$uid,$type,$graphic,$videoEmbLink,$status);
        $pstmtInsert->execute();
        $pstmtInsert->store_result();
        
        if($pstmtInsert->affected_rows > 0){
            return TRUE;
        }else{
            return FALSE;
        }
        
        $pstmtInsert->close();
        
    }
    
    public function getImageType($type){
        $image = NULL;
        
        if($this->type=='General'){
            $type = '<img src="img/AnnouncementGeneral.png" />';
        }else if($this->type=='Event'){
            $type = '<img src="img/AnnouncementEvent.png" />';
        }else{
            $type = '<img src="img/AnnouncementService.png" />';
        }      
        
        return $type;
    }
    
    public function getAnnouncementDetails($aid1){
        $status = 'Available';
        $pstmtSelect = $this->con->prepare($this->selectAnnounceModify);
        $pstmtSelect->bind_param('is', $aid1, $status);
        $pstmtSelect->execute();
        $pstmtSelect->bind_result($this->aid, $this->title, $this->message, $this->publishDate, $this->uid, $this->type, $this->graphic, $this->videoEmbLink);
        $pstmtSelect->store_result();

        if($pstmtSelect->fetch()){
            $result = array(
                'aid' => $this->aid,
                'title' => $this->title,
                'message' => $this->message,
                'publishDate' => $this->publishDate,
                'uid' => $this->uid,
                'type' => $this->type,
                'graphic' => $this->graphic,
                'videoEmbLink' => $this->videoEmbLink
            );
        }
        return $result;
        $pstmtSelect->close();
    }
    
    
    public function listSelectUpdateAnnouncement(){
        $content = NULL;
        $status = 'Available';
        $pstmtSelect = $this->con->prepare($this->selectListsAnnouncements2);
        $pstmtSelect->bind_param('s', $status);
        $pstmtSelect->bind_result($this->aid,$this->title,$this->message,$this->publishDate,$this->uid,$this->type, $this->graphic, $this->videoEmbLink);
        $pstmtSelect->execute();
        $pstmtSelect->store_result();
        
        $content .='<div class="large-15 large-offset-1 columns" id="formsec">';
        $content .='<h3>Announcement</h3>';
        $content .='<fieldset>';
        $content .='<legend>Select to modify</legend>';
        $content .='<div class="row">';
        $content .='<b>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ID</b>';
        $content .='&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>Date</b>';
        $content .='&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<b>Title</b>';
        $content .='</div>';
        while($pstmtSelect->fetch()){
            $content .='<div class="row">';
            $content .='<div class="large-15 columns">';
            $content .='&nbsp;<a href="announcementMaintenance.php?mode=4&aid='.$this->aid.'">'.htmlspecialchars($this->aid).'</a>';
            $content .='&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'.$this->publishDate.'';
            $content .='&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href="announcementMaintenance.php?mode=4&aid='.$this->aid.'">'.htmlspecialchars($this->title).'</a><br />';
            $content .='</div>';
            $content .='</div>';
        }
        $content .='</table>';
        $content .='</div>';
        $content .='<hr />';
        $content .='</fieldset>';

        
        echo $content;
        $pstmtSelect->close();
    }
    
    
    public function getAnnouncementArrayDelete(){
        $count = 0;
        $i = 0;
        $anntemp = array();
        $ann = null;
        $content = null;
        $status = 'Available';
        $pstmtSelect = $this->con->prepare($this->selectListDeleteAnnouncement);
        $pstmtSelect->bind_param('s', $status);
        $pstmtSelect->execute();
        $pstmtSelect->bind_result($anntemp['aid'], $anntemp['title'], $anntemp['message'], $anntemp['publishDate'], $anntemp['uid'], $ann['type'], $anntemp['graphic'], $anntemp['videoEmbLink']);
        $pstmtSelect->store_result();

        while($pstmtSelect->fetch()){
            $count++;
            $i++;
            foreach ($anntemp as $key => $value) {
                $row[$key] = $value;
            }
            $ann = $row;
            $content .='<li style="list-style-type:none">';
            $content .='<input type="checkbox" id="c'.$i.'" name="c[]" value="'.$row['aid'].'"> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'.$row['aid'].'  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; '.$row['publishDate'].' &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;  '.substr($row['title'],1,50).'';
        }
        $this->count = $count;
        $content .='<input type="hidden" id="counts" name="counts" value="'.$this->getCountDelete().'"/>';
        
        echo $content;  

        $pstmtSelect->close();
    }
    
    public function getCountDelete(){
         return $this->count;
    }
    
    public function deleteAnnouncement($aid){
        $status = 'Deleted';
        $pstmtUpdate = $this->con->prepare($this->updateRemoveAnnouncement);
        $pstmtUpdate->bind_param('si', $status, $aid);
        $pstmtUpdate->execute();
        $pstmtUpdate->store_result();
        
        if($pstmtUpdate->affected_rows >0){
            return TRUE;
        }else{
            return FALSE;
        }
    }
    
    public function getAnnouncementArrayRestore(){
        $count = 0;
        $i = 0;
        $anntemp = array();
        $ann = null;
        $content = null;
        $status = 'Deleted';
        $pstmtSelect = $this->con->prepare($this->selectListRestoreAnnouncement);
        $pstmtSelect->bind_param('s', $status);
        $pstmtSelect->execute();
        $pstmtSelect->bind_result($anntemp['aid'], $anntemp['title'], $anntemp['message'], $anntemp['publishDate'], $anntemp['uid'], $ann['type'], $anntemp['graphic'], $anntemp['videoEmbLink']);
        $pstmtSelect->store_result();

        while($pstmtSelect->fetch()){
            $count++;
            $i++;
            foreach ($anntemp as $key => $value) {
                $row[$key] = $value;
            }
            $ann = $row;
            $content .='<li style="list-style-type:none">';
            $content .='<input type="checkbox" id="c'.$i.'" name="c[]" value="'.$row['aid'].'"> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'.$row['aid'].'  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; '.$row['publishDate'].'  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'.$row['title'].'';
        }
        $this->count = $count;
        $content .='<input type="hidden" id="counts" name="counts" value="'.$this->getCountRestore().'"/>';
        
        echo $content;  

        $pstmtSelect->close();
    }
    
    public function getCountRestore(){
         return $this->count;
    }
    
    public function restoreAnnouncement($aid){
        $status = 'Available';
        $pstmtUpdate = $this->con->prepare($this->updateRestoreAnnouncement);
        $pstmtUpdate->bind_param('si', $status, $aid);
        $pstmtUpdate->execute();
        $pstmtUpdate->store_result();
        
        if($pstmtUpdate->affected_rows >0){
            return TRUE;
        }else{
            return FALSE;
        }
    }
    
    public function emailPromotion(){
        $temp = array();
        $status = 'Available';
        $content = '';
        $pstmtSelect = $this->con->prepare($this->selectEmailPromotion);
        $pstmtSelect->bind_param('s', $status);
        $pstmtSelect->execute();
        $pstmtSelect->bind_result($temp['aid'], $temp['title'], $temp['message'], $temp['publishDate'], $temp['uid'], $temp['type'], $temp['graphic'], $temp['videoEmbLink']);
        $content .= '';
        while($pstmtSelect->fetch()){
            
        }
        
        
    }
    
    public function populateAnnouncementId(){
        $status = 'Available';
        $content = '';
        $temp = array();
        $arrays = null;
        $pstmtSelect = $this->con->prepare($this->selectAllAvailableAnnouncement);
        $pstmtSelect->bind_param('s', $status);
        $pstmtSelect->bind_result($temp['aid'], $temp['title'], $temp['message'], $temp['publishDate'], $temp['uid'], $temp['type'], $temp['graphic'], $temp['videoEmbLink']);  
        $pstmtSelect->execute();
        $pstmtSelect->store_result();
      
        while($pstmtSelect->fetch()){
            foreach ($temp as $key => $value) {
                $row[$key] = $value;
            }
            $arrays = $row;
            $content .='<option value="'.$row['aid'].'">'.$row['aid'].'</option>'; 
        }
 
        echo $content;
        $pstmtSelect->close();
    }
   
    public function populateAnnouncementById($aid){
        $status = 'Available';
        $temp = array();
        $arrays = null;
        $pstmtSelect = $this->con->prepare($this->selectAllAvailableAnnouncement2);
        $pstmtSelect->bind_param('is',$aid,$status);
        $pstmtSelect->store_result();
        $pstmtSelect->bind_result($temp['aid'], $temp['title'], $temp['message'], $temp['publishDate'], $temp['uid'], $temp['type'], $temp['graphic'], $temp['videoEmbLink']); 
        $pstmtSelect->execute();
        
        if($pstmtSelect->fetch()){
            foreach ($temp as $key => $value) {
                $row[$key] = $value;
            }
            $arrays = $row;
            $this->aid = $row['aid'];
            $this->title = $row['title'];
            $this->message = $row['message'];
            $this->publishDate = $row['publishDate'];
            $this->uid = $row['uid'];
            $this->type = $row['type'];
            $this->graphic = $row['graphic'];
            $this->videoEmbLink = $row['videoEmbLink'];
            
            return TRUE;
        }else{
            return FALSE;
        }
        
        $pstmtSelect->close();
        
    }
    
    public function updateAnnouncement($title,$message,$type,$graphic,$videoEmbLink,$aid){
        $pstmtUpdate = $this->con->prepare($this->updateAnnouncement);
        $pstmtUpdate->bind_param('sssssi', $title,$message,$type,$graphic,$videoEmbLink,$aid);
        $pstmtUpdate->execute();
        
        if($pstmtUpdate->affected_rows > 0){
            return TRUE;
        }else{
            return FALSE;
        }
        
        
    }
    
    public function getAid() {
        return $this->aid;
    }

    public function setAid($aid) {
        $this->aid = $aid;
    }

    public function getMessage() {
        return $this->message;
    }

    public function setMessage($message) {
        $this->message = $message;
    }

    public function getPublishDate() {
        return $this->publishDate;
    }

    public function setPublishDate($publishDate) {
        $this->publishDate = $publishDate;
    }

    public function getTitle() {
        return $this->title;
    }

    public function setTitle($title) {
        $this->title = $title;
    }

    public function getUid() {
        return $this->uid;
    }

    public function setUid($uid) {
        $this->uid = $uid;
    }

    public function getType() {
        return $this->type;
    }

    public function setType($type) {
        $this->type = $type;
    }

    public function getGraphic() {
        return $this->graphic;
    }

    public function setGraphic($graphic) {
        $this->graphic = $graphic;
    }

    public function getVideoEmbLink() {
        return $this->videoEmbLink;
    }

    public function setVideoEmbLink($videoEmbLink) {
        $this->videoEmbLink = $videoEmbLink;
    }

    public function sendEmail($receiver, $title, $content) {
        $transport = Swift_SmtpTransport::newInstance('smtp.gmail.com', 465, "ssl")
                ->setUsername('railway.no.reply@gmail.com')
                ->setPassword('Railway0123');

        $mailer = Swift_Mailer::newInstance($transport);

        $message = Swift_Message::newInstance($title)
                ->setFrom(array('railway.no.reply@gmail.com' => 'Railway Announcement'))
                ->setTo($receiver)
                ->setBody($content, 'text/html');

        $result = $mailer->send($message);
        return $result;
    }
    

    
    
}
?>
